/**/
package com.compuvest.webstat;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

import javax.naming.Context;
import javax.naming.InitialContext;

import java.io.IOException;

import java.util.Map;
import java.util.HashMap;

/**
 *http://www.quirksmode.org/js/detect.html
 * 
 * BROWSER
  {ident:"Explorer",    nm:1},
  {ident:"Firefox",     nm:2},
  {ident:"Chrome",      nm:3},
  {ident:"OmniWeb",     nm:4},
  {ident:"Safari",      nm:5},
  {ident:"Opera",       nm:6},
  {ident:"iCab",        nm:7},
  {ident:"Konqueror",   nm:8},
  {ident: "Camino",     nm:9},
  {ident:"Netscape",    nm:10},
  {ident: "Mozilla",    nm:11},
 *
 * OS
  {ident:"Windows",     nm:1},
  {ident:"Linux",       nm:2},
  {ident:"Mac",         nm:3},
  {ident:"iPod",        nm:4}
 *

 * 
 * 
  +------+-----------+---------------+--------------+-------+------------+------+
  | id   | advert_id | mfrNumber     | accwareSKU   | count | date       | send |
  +------+-----------+---------------+--------------+-------+------------+------+

 * 
 * 
 *
 */
public class SnifferTest extends HttpServlet {

    private DataSource ds;
    private Map<String,String> advertTable;

// <editor-fold defaultstate="collapsed" desc="init(ServletConfig config)">
    @Override
    public void init(ServletConfig config) throws ServletException {
        super.init(config);

        try {
            Context initCtx = new InitialContext();
            ds = (DataSource)initCtx.lookup("jdbc/mysql");
        }
        catch(Exception e) { System.out.println("WebStatus::Sniffer::init\n" + e); }

        // fill advert table fro convert name to id
        advertTable = getAdvertTable();

        System.out.println("WebStatus::Sniffer::MySQL Version=" + getVersion() + ":" + advertTable.size());
     }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="processRequest(HttpServletRequest request, HttpServletResponse response)">
    /**
    * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
    * @param request servlet request
    * @param response servlet response
    */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        response.setContentType("text/html;charset=UTF-8");

        System.out.println("SnifferTest:"
                // main params
                + "\nwebid=" + request.getParameter("wid")  // web site id (req) def=0
                + "\naid=" + request.getParameter("aid")    // advert id (req) def=''
                + ":" + (advertTable.containsKey(request.getParameter("aid")) ? advertTable.get(request.getParameter("aid")) : "1")
                + "\nsrc=" + request.getParameter("src")    // Search= (req) def=''
                + "\nsku=" + request.getParameter("sku")    // accwareSKU (req) def=''
                // additions params
                + "\ncvip=" + request.getParameter("cvip")  // ip address (integer format)
                + "\npg=" + request.getParameter("pg")      // count of page
                + "\nrf=" + request.getParameter("rf")      // comming from
                + "\nurl=" + request.getParameter("url")    // current url
                + "\npage=" + request.getParameter("page")  // current page
                + "\nbws=" + request.getParameter("bws")    // browser info

//                + "\nid=" + request.getParameter("id")
//                + "\nsid=" + request.getParameter("sid")
        );

        updateMfrNumberStatTest(
            request.getParameter("wid"),
            (advertTable.containsKey(request.getParameter("aid")) ? advertTable.get(request.getParameter("aid")) : "1"),
            request.getParameter("src"),
            request.getParameter("sku")
        );
/*
        setStat(request.getParameter("id"),
                    request.getParameter("wid"),
                    request.getParameter("cvip"),
                    request.getParameter("sid"),
                    request.getParameter("nav"),
                    request.getParameter("ver"),
                    request.getParameter("os"),
                    request.getParameter("pg"),
                    request.getParameter("rf"),
                    request.getParameter("url"),
                    request.getParameter("srch"),
                    request.getParameter("adv"),
                    request.getParameter("page")
                    );
*/
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getVersion()">
//------------------------------------------------
//
    private String getVersion() {

        String result = "";
        Connection conn = null;
        try {
            conn = ds.getConnection();

            PreparedStatement ps = conn.prepareStatement("SELECT VERSION() version");
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                result = rs.getString("version");
            }
            rs.close();
            ps.close();
        }
        catch(SQLException e) { System.out.println("Sniffer::getVersion\n" + e); }
        finally {
            if(conn != null) try { conn.close(); conn = null; } catch(SQLException ignore) { }
        }

        return result;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getAdvert()">
//------------------------------------------------
//
    private Map<String,String> getAdvertTable() {

        Map<String,String> adverts = new HashMap<String,String>();
        Connection conn = null;
        try {
            conn = ds.getConnection();

            PreparedStatement ps = conn.prepareStatement("SELECT advert_id,name FROM sklad.advert");
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                adverts.put(rs.getString("name"), rs.getString("advert_id"));
            }
            rs.close();
            ps.close();
        }
        catch(SQLException e) { System.out.println("Sniffer::getAdvertTable\n" + e); }
        finally {
            if(conn != null) try { conn.close(); conn = null; } catch(SQLException ignore) { }
        }

        return adverts;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="setStat()">
//------------------------------------------------
//
    private void setStat(
        String unixTime,// uniq id for refresh IE
        String webid,   // website uniq ID
        String cvip,    // ip address
        String sid,     // session id
        String nav,     // navigator
        String ver,     // navigator version
        String os,      // o system
        String pg,      // how many visit to pages
        String rf,      // reference
        String url,     // full website URL
        String srch,    // come from advert site what serch ?
        String adv,     // come from advert site
        String page)    // shot URL (like /Search.jsp  or /index.jsp or /
    {

        Connection conn = null;
        try {
            conn = ds.getConnection();

            CallableStatement cs = conn.prepareCall("{ call webstat.setStat(?,?,?,?,?,?,?,?,?,?,?,?) }");
            cs.setString(1, webid);
            cs.setString(2, cvip);
            cs.setString(3, sid);
            cs.setString(4, nav);
            cs.setString(5, ver);
            cs.setString(6, os);
            cs.setString(7, pg);
            cs.setString(8, rf);
            cs.setString(9, url);
            cs.setString(10, srch);
            cs.setString(11, adv);
            cs.setString(12, page);

            cs.executeUpdate();

            cs.close();
        }
        catch(SQLException e) { System.out.println("Sniffer::setStat\n" + e); }
        finally {
            if(conn != null) try { conn.close(); } catch(SQLException ignore) { }
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="updateMfrNumberStat(String clientID, String advertName, String mfrNumber, String accwareSKU)">
//------------------------------------------------
//
    private void updateMfrNumberStatTest(String websiteID, String advertID, String mfrNumber, String accwareSKU) {

        if(websiteID != null && websiteID.length() > 0 && mfrNumber != null && mfrNumber.length() > 0 ) {

            Connection conn = null;
            try {
                conn = ds.getConnection();
                                                                   // websiteID, advertID, mfrNumber, AccewareSKU
                CallableStatement cs = conn.prepareCall("{ call webstat.setMfrNumberStatTest(?,?,?,?) }");
                cs.setString(1, websiteID);
                cs.setString(2, advertID);
                cs.setString(3, mfrNumber);
                cs.setString(4, accwareSKU);

                cs.executeUpdate();

                cs.close();
            }
            catch(SQLException e) { System.out.println("Sniffer::updateMfrNumberStatTest\n" + e); }
            finally {
                if(conn != null) try { conn.close(); } catch(SQLException ignore) { }
            }
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="HttpServlet methods.">
    /**
    * Handles the HTTP <code>GET</code> method.
    * @param request servlet request
    * @param response servlet response
    */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)   throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
    * Handles the HTTP <code>POST</code> method.
    * @param request servlet request
    * @param response servlet response
    */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)  throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
    * Returns a short description of the servlet.
    */
    @Override
    public String getServletInfo() {
        return "Sniffer";
    }

    @Override
    public void destroy() {
        super.destroy();

        ds = null;

    }
// </editor-fold>
}
